package xyz.csmcool.imuser.mapper;

import org.apache.ibatis.annotations.*;
import xyz.csmcool.imuser.entity.Users;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import xyz.csmcool.imuser.entity.vo.UserInfoVo;
import xyz.csmcool.imuser.entity.vo.UserSimpleVo;
import xyz.csmcool.imutil.dto.UserDto;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author csmcool
 * @since 2022-07-31
 */
@Mapper
public interface UsersMapper extends BaseMapper<Users> {

    /**
     * 根据用户名和密码查询是否存在
     * @param email 电子邮箱

     * @return 用户id
     */
    @Select("select id from sjim_users where email=#{email}")
    Integer selectIdByEmailAndPassword(@Param("email") String email);

    /**
     * 根据用户邮箱和密码返回基本信息
     * @param email
     * @param password
     * @return
     */
    @Select("SELECT id,user_name AS userName,available_status AS availableStatus FROM sjim_users where email=#{email} and password=#{password}")
    Users selectUserInfoByEmailAndPassword(@Param("email") String email,@Param("password") String password);

    /**
     * 添加新的用户
     * @param username 用户名
     * @param password 密码
     * @param email 电子邮件
     * @return 是否成功
     */
    @Insert("INSERT INTO sjim_users(`user_name`,`email`,`password`,`verify_code`) VALUES(#{username},#{email},#{password},#{code})")
    Boolean insertNewUser(@Param("username")String username,@Param("password")String password,
                          @Param("email")String email,@Param("code") String code);

    /**
     * 根据邮箱查找用户id
     * @param email 电子邮箱
     * @return id
     */
    @Select("select id from sjim_users where email=#{email}")
    Integer selectIdByEmail(@Param("email") String email);

    /**
     * change user's password to a default value
     * @param id user'id
     * @param password password
     * @return flag for action
     */
    @Update("UPDATE sjim_users SET `password`=#{password} WHERE `id`=#{id}")
    Boolean updateUserPassword(@Param("id") Integer id,@Param("password") String password);


    /**
     * 激活邮箱
     * @param email
     * @param code
     * @return
     */
    @Update("UPDATE sjim_users SET `available_status`=1 WHERE `verify_code`=#{code} AND email=#{email} ")
    Boolean activeEmail(@Param("email") String email, @Param("code") String code);


    /**
     * 更新用户信息
     * @param userInfoVo
     * @return
     */
    @Update("<script>" +
            "UPDATE `sjim_users`" +
            "<set>" +
            
            "<if test='userName!=null'>" +
            "user_name=#{userName}," +
            "</if>" +

            "<if test='firstName!=null'>" +
            "first_name=#{firstName}," +
            "</if>" +

            "<if test='lastName!=null'>" +
            "last_name=#{lastName}," +
            "</if>" +

            "<if test='sex!=null'>" +
            "sex=#{sex}," +
            "</if>" +

            "<if test='avatar!=null'>" +
            "avatar=#{avatar}," +
            "</if>" +

            "<if test='about!=null'>" +
            "about=#{about}," +
            "</if>" +

            "<if test='country!=null'>" +
            "country=#{country}" +
            "</if>" +

            "</set>" +
            "WHERE `id` = #{id}" +
            "</script>")
    Boolean updateUserInfo(UserInfoVo userInfoVo);

    /**
     * 查询用户
     * @param ids
     * @return
     */
    @Select("<script>" +
            "SELECT * from sjim_users WHERE id" +
            "<if test='ids.size() > 0' >" +
            "IN" +
            "</if>" +
            "<foreach item='item' index='index' collection='ids' open='(' separator=',' close=')' >" +
            "#{item}" +
            "</foreach>" +
            "</script>")
    List<UserDto> selectRoomUser(@Param("ids") List<Integer> ids);


    /**
     * 通过用户名查询用户
     * @param ids
     * @param name
     * @param page
     * @param limit
     * @return
     */
    @Select("<script>" +
            "SELECT * from sjim_users WHERE user_name LIKE concat('%',#{name},'%') " +
            "<if test='ids.size() > 0' >" +
            "AND id IN" +
            "</if>" +
            "<foreach item='item' index='index' collection='ids' open='(' separator=',' close=')' >" +
            "#{item}" +
            "</foreach>" +
            "ORDER BY create_time DESC LIMIT #{page},#{limit}" +
            "</script>")
    List<UserDto> selectRoomUserByName(@Param("ids") List<Integer> ids,
                                       @Param("name") String name,
                                       @Param("page") Long page,
                                       @Param("limit") Long limit);


    /**
     * 查询用户总数
     * @param ids
     * @param name
     * @return
     */
    @Select("<script>" +
            "SELECT count(*) from sjim_users WHERE user_name LIKE concat('%',#{name},'%') " +
            "<if test='ids.size() > 0' >" +
            "AND id IN" +
            "</if>" +
            "<foreach item='item' index='index' collection='ids' open='(' separator=',' close=')' >" +
            "#{item}" +
            "</foreach>" +
            "</script>")
    Integer selectRoomUserCount(@Param("ids") List<Integer> ids,
                                       @Param("name") String name);

    /**
     * 搜索一个人简单的信息
     * @param id
     * @return
     */
    @Select("SELECT user_name userName,avatar,user_status userStatus,sex,about " +
            " FROM `sjim_users` WHERE id = #{id} LIMIT 1;")
    UserSimpleVo selectOneUserSimpleInfo(@Param("id") Integer id);

    /**
     * 查找用户
     * @param page
     * @param limit
     * @param userName
     * @param email
     * @return
     */
    @Select("SELECT * FROM ( " +
            "SELECT id,user_name,avatar,about FROM `sjim_users` FORCE INDEX(index_user_name) " +
            "WHERE user_name LIKE concat('',#{name},'%')" +
            "UNION ALL " +
            "SELECT id,user_name,avatar,about FROM `sjim_users` FORCE INDEX(index_email) " +
            "WHERE email LIKE concat('',#{email},'%')" +
            ") a LIMIT #{page},#{limit}")
    List<Users> searchUserByNameOrEmail(@Param("page") Long page,
                                        @Param("limit") Long limit,
                                        @Param("name") String userName,
                                        @Param("email") String email);

    /**
     * 查找用户count
     * @param userName
     * @param email
     * @return
     */
    @Select("SELECT COUNT(*) FROM ( " +
            "SELECT id,user_name,avatar,about FROM `sjim_users` FORCE INDEX(index_user_name) " +
            "WHERE user_name LIKE concat('',#{name},'%')" +
            "UNION ALL " +
            "SELECT id,user_name,avatar,about FROM `sjim_users` FORCE INDEX(index_email) " +
            "WHERE email LIKE concat('',#{email},'%')" +
            ") a")
    Long searchUserCountByNameOrEmail(@Param("name") String userName,
                                        @Param("email") String email);
}
